package limeirafol.bean;

import java.sql.ResultSet;
import java.sql.Statement;
import lbframework.classe.Conexao;
import lbframework.classe.LBException;
import lbframework.classe.Util;
import limeirafol.Global;
import limeirafol.remote.sistema.EmailVO;

public class BeanSistema {

    public void salvarSenha(String i_senhaAtual, String i_senhaNova) throws Exception {
        Statement stm = null;
        StringBuilder sql = null;
        ResultSet rst = null;

        try {
            Conexao.begin();
            stm = Conexao.createStatement();

            sql = new StringBuilder();
            sql.append("SELECT id FROM usuario WHERE id = " + Global.idUsuario + " AND senha = MD5('" + i_senhaAtual + "')");

            rst = stm.executeQuery(sql.toString());

            if (!rst.next()) {
                throw new LBException("A senha atual está incorreta!");
            }

            sql = new StringBuilder();
            sql.append("UPDATE usuario SET senha = MD5('" + i_senhaNova + "') WHERE id = " + Global.idUsuario);

            stm.execute(sql.toString());

            stm.close();
            Conexao.commit();

        } catch (Exception ex) {
            Conexao.rollback();
            throw ex;
        }
    }

    public EmailVO carregarEmail(int i_idUsuario) throws Exception {
        Statement stm = null;
        ResultSet rst = null;

        stm = Conexao.createStatement();

        rst = stm.executeQuery("SELECT * FROM email WHERE id_usuario = " + i_idUsuario);

        EmailVO oEmail = new EmailVO();

        if (rst.next()) {
            oEmail.email = rst.getString("email");
            oEmail.senha = Util.descriptografarBase64(rst.getString("senha"));
            oEmail.servidor = rst.getString("servidor");
            oEmail.porta = rst.getInt("porta");
            oEmail.requerAutenticacao = rst.getBoolean("requerautenticacao");
            oEmail.tls = rst.getBoolean("tls");
        }

        stm.close();

        return oEmail;
    }

    public void salvarEmail(EmailVO i_email) throws Exception {
        Statement stm = null;
        StringBuilder sql = null;
        ResultSet rst = null;

        try {
            Conexao.begin();
            stm = Conexao.createStatement();

            rst = stm.executeQuery("SELECT id FROM email WHERE id_usuario = " + i_email.idUsuario);

            if (rst.next()) {
                sql = new StringBuilder();
                sql.append("UPDATE email SET");
                sql.append(" email = '" + i_email.email + "',");
                sql.append(" senha = '" + Util.criptografarBase64(i_email.senha) + "',");
                sql.append(" servidor = '" + i_email.servidor + "',");
                sql.append(" porta = " + i_email.porta + ",");
                sql.append(" requerautenticacao = " + i_email.requerAutenticacao + ",");
                sql.append(" tls = " + i_email.tls);
                sql.append(" WHERE id_usuario = " + i_email.idUsuario);

                stm.execute(sql.toString());


            } else {
                sql = new StringBuilder();
                sql.append("INSERT INTO email (id_usuario, email, senha, servidor, porta, requerautenticacao, tls) VALUES ( ");
                sql.append(i_email.idUsuario + ",");
                sql.append("'" + i_email.email + "',");
                sql.append("'" + Util.criptografarBase64(i_email.senha) + "',");
                sql.append("'" + i_email.servidor + "',");
                sql.append(i_email.porta + ",");
                sql.append(i_email.requerAutenticacao + ",");
                sql.append(i_email.tls + ")");

                stm.execute(sql.toString());

            }

            stm.close();
            Conexao.commit();

        } catch (Exception ex) {
            Conexao.rollback();
            throw ex;
        }
    }
}
